﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections;
using System.Web.Configuration;

namespace Continental.Code.DAL
{
    public static class DataProvider
    {
        public static String ConnectionString
        {
            get
            {
                return WebConfigurationManager.ConnectionStrings["ContinentalConnectionString"].ConnectionString;
            }
        }

        public static void ExecuteNoneQuery(SqlCommand command)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            command.Connection = conn;
            conn.Open();
            command.ExecuteNonQuery();
            conn.Close();
        }

        public static Int32 ExecuteScalar(SqlCommand command)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            command.Connection = conn;
            conn.Open();
            Int32 theResult = Int32.Parse(command.ExecuteScalar().ToString());
            conn.Close();
            return theResult;
        }

        public static DataTableReader ExecuteReader(SqlCommand command)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            command.Connection = conn;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(ds);
            DataTableReader reader = ds.CreateDataReader();
            return reader;
        }

        public static DataSet ExecuteDataSet(SqlCommand command)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            command.Connection = conn;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(ds);
            return ds;
        }
        public static DataSet ExecuteDataSet(SqlCommand command, out Int32 totalRecord)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            command.Connection = conn;
            SqlParameter totalRecordParam = new SqlParameter("@TotalRecord", SqlDbType.Int);
            totalRecordParam.Direction = ParameterDirection.Output;
            command.Parameters.Add(totalRecordParam);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(ds);
            totalRecord = Int32.Parse(totalRecordParam.Value.ToString());
            return ds;
        }

        public static SqlConnection GetSqlConnection()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConnectionString;
            conn.Open();
            return conn;
        }

        public static SqlCommand GetSqlCommandExcuteByText(string cmdText)
        {
            SqlCommand cmd = new SqlCommand(cmdText, GetSqlConnection());
            return cmd;
        }

        public static SqlCommand GetSqlCommandExcuteByProc(string procName)
        {
            SqlCommand cmd = new SqlCommand(procName, GetSqlConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            return cmd;
        }

        public static void ExcuteProc(string _procName, Hashtable para)
        {
            SqlCommand cmd = GetSqlCommandExcuteByProc(_procName);
            SqlParameter sqlPara;
            // get all paramenter
            IDictionaryEnumerator para_enumerator = para.GetEnumerator();

            while (para_enumerator.MoveNext())
            {
                sqlPara = new SqlParameter(para_enumerator.Key.ToString(), para_enumerator.Value);
                // add paramenter into SqlCommand object
                cmd.Parameters.Add(sqlPara);
                sqlPara = null;
            }
            // excute proc

            int i = cmd.ExecuteNonQuery();

        }

        public static void GetDataSetByCommandText(string cmdText, out DataSet ds)
        {
            SqlCommand cmd = new SqlCommand();
            DataSet dsTemp = new DataSet();
            cmd = GetSqlCommandExcuteByText(cmdText);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dsTemp);
            ds = dsTemp;
        }

        //public static void GetDataSetByProc(string procName, out DataSet ds)
        //{
        //    SqlCommand cmd = new SqlCommand();
        //    DataSet dsTemp = new DataSet();
        //    cmd = GetSqlCommandExcuteByText(cmdText);
        //    SqlDataAdapter da = new SqlDataAdapter(cmd);
        //    da.Fill(dsTemp);
        //    ds = dsTemp;
        //}
    }
}